﻿using System;
using System.Collections.Generic;
using System.Text;
using BarCode.Bean;
using BarCode.Common;
using System.Data;
using BarCode.Module;
using MySql.Data.MySqlClient;
using System.Windows.Forms;

namespace BarCode.AccessDao
{
    public class DB01Dao
    {
        private static readonly log4net.ILog LogUtil = log4net.LogManager.GetLogger(typeof(DB01Dao));

        private MySqlHelper db01;
        public DB01Dao(string serverIp, string userId, string userPwd)
        {
            db01 = new MySqlHelper(serverIp, "db_01", userId, userPwd);
        }

        /// <summary>
        /// 判断是否可链接
        /// </summary>
        /// <returns></returns>
        public bool EnableConnect()
        {
            return db01.EnableConnect();
        }



        /// <summary>
        /// 获取用户信息
        /// </summary>
        /// <returns></returns>
        public List<UserBean> getUserInfoList(string lineNo)
        {
            List<UserBean> retList = new List<UserBean>();
            MySqlParameter sqlParam = new MySqlParameter("@lineNo", lineNo);
            DataSet retSet = db01.GetDataSet(CommandType.Text, "select * from client_user where lineNo=@lineNo", new MySqlParameter[] { sqlParam });
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                foreach (DataRow tmpRow in dt.Rows)
                {
                    UserBean tmpBean = new UserBean();
                    tmpBean.User = tmpRow["username"].ToString();
                    tmpBean.Password = tmpRow["userpassword"].ToString();
                    tmpBean.Level = tmpRow["level"].ToString();
                    tmpBean.lineno = tmpRow["lineNo"].ToString();
                    retList.Add(tmpBean);
                }
            }
            return retList;
        }

        /// <summary>
        /// 获取用户信息
        /// </summary>
        /// <returns></returns>
        public UserBean getUserInfo(string lineNo, string userName)
        {
            MySqlParameter[] sqlParams = new MySqlParameter[2];
            sqlParams[0] = new MySqlParameter("@lineNo", lineNo);
            sqlParams[1] = new MySqlParameter("@username", userName);
            DataSet retSet = db01.GetDataSet(CommandType.Text, "select * from client_user where lineNo=@lineNo and username=@username",sqlParams);
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    DataRow tmpRow = dt.Rows[0];
                    UserBean tmpBean = new UserBean();
                    tmpBean.User = tmpRow["username"].ToString();
                    tmpBean.Password = tmpRow["userpassword"].ToString();
                    tmpBean.Level = tmpRow["level"].ToString();
                    tmpBean.lineno = tmpRow["lineNo"].ToString();
                    return tmpBean;
                }
            }
            return null;
        }


        /// <summary>
        /// 获取偏移量信息
        /// </summary>
        /// <returns></returns>
        public OffSetInfo getOffSetInfo()
        {
            OffSetInfo retInfo = new OffSetInfo();
            retInfo.Init();
            DataSet retSet = db01.GetDataSet(CommandType.Text, "select * from tbloffset");
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                foreach (DataRow tmpRow in dt.Rows)
                {
                    string position = Convert.ToString(tmpRow["workposition"]);
                    if (position.Equals("jx"))
                    {
                        retInfo.jxoffset = Convert.ToInt32(tmpRow["offsetvalue"]);
                    }
                    else if (position.Equals("jk"))
                    {
                        retInfo.jkoffset = Convert.ToInt32(tmpRow["offsetvalue"]);
                    }
                    else if (position.Equals("jxjk"))
                    {
                        retInfo.jxjkoffset = Convert.ToInt32(tmpRow["offsetvalue"]);
                    }
                    else if (position.Equals("base"))
                    {
                        retInfo.baseoffset = Convert.ToInt32(tmpRow["offsetvalue"]);
                    }
                    else if (position.Equals("zjmf"))
                    {
                        retInfo.zjmfoffset = Convert.ToInt32(tmpRow["offsetvalue"]);
                    }
                    else if (position.Equals("pack"))
                    {
                        retInfo.packoffset = Convert.ToInt32(tmpRow["offsetvalue"]);
                    }
                    else if (position.Equals("smf"))
                    {
                        retInfo.smfoffset = Convert.ToInt32(tmpRow["offsetvalue"]);
                    }
                }
            }
            return retInfo;

        }

        /// <summary>
        /// 获取车辆牌照信息
        /// </summary>
        /// <returns></returns>
        public List<LicenceInfo> getLicenceInfo()
        {
            List<LicenceInfo> retList = new List<LicenceInfo>();
            DataSet retSet = db01.GetDataSet(CommandType.Text, "select * from tbllicence where isout='未装车' and delbz='0'", new MySqlParameter[] { });
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                foreach (DataRow tmpRow in dt.Rows)
                {
                    LicenceInfo tmpBean = new LicenceInfo();
                    tmpBean.packID_start = Convert.ToString(tmpRow["packID_start"]);
                    tmpBean.packID_end = Convert.ToString(tmpRow["packID_end"]);
                    tmpBean.licence = Convert.ToString(tmpRow["licence"]);
                    tmpBean.isout = Convert.ToString(tmpRow["isout"]);
                    tmpBean.delbz = Convert.ToString(tmpRow["delbz"]);
                    tmpBean.operatorName = Convert.ToString(tmpRow["operator"]);
                    tmpBean.opdt = Convert.ToString(tmpRow["opdt"]);
                    retList.Add(tmpBean);
                }
            }
            return retList;

        }

        /// <summary>
        /// 获取待水密封箱表信息
        /// </summary>
        /// <returns></returns>
        public List<MnoprnPreInfo> getMnoprnPreInfo()
        {
            List<MnoprnPreInfo> retList = new List<MnoprnPreInfo>();
            // 2019-03-14 逆光微信Txt文件  读取批次条件把‘istest=已测’去掉
            //DataSet retSet = db01.GetDataSet(CommandType.Text, "select * from tblmnoprn_pre where istest='已测' and ispack='未封' and delbz='0'", new MySqlParameter[] { });
            DataSet retSet = db01.GetDataSet(CommandType.Text, "select * from tblmnoprn_pre where ispack='未封' and delbz='0'", new MySqlParameter[] { });
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                foreach (DataRow tmpRow in dt.Rows)
                {
                    MnoprnPreInfo tmpBean = new MnoprnPreInfo();
                    tmpBean.listID = Convert.ToInt32(tmpRow["listID"]);
                    tmpBean.orderID = Convert.ToString(tmpRow["orderID"]);
                    tmpBean.meters_code = Convert.ToString(tmpRow["meters_code"]);
                    tmpBean.meters_directions = Convert.ToString(tmpRow["meters_directions"]);
                    tmpBean.meters_IDstart = Convert.ToString(tmpRow["meters_IDstart"]);
                    tmpBean.meters_IDend = Convert.ToString(tmpRow["meters_IDend"]);
                    tmpBean.packID_start = Convert.ToString(tmpRow["packID_start"]);
                    tmpBean.packID_end = Convert.ToString(tmpRow["packID_end"]);
                    tmpBean.meters_tolmax = Convert.ToDouble(tmpRow["meters_tolmax"]);
                    tmpBean.meters_tolmin = Convert.ToDouble(tmpRow["meters_tolmin"]);
                    tmpBean.customer_code = Convert.ToString(tmpRow["customer_code"]);
                    tmpBean.meters_ploss = Convert.ToDouble(tmpRow["meters_ploss"]);
                    tmpBean.istest = Convert.ToString(tmpRow["istest"]);
                    tmpBean.ispack = Convert.ToString(tmpRow["ispack"]);
                    tmpBean.delbz = Convert.ToString(tmpRow["delbz"]);
                    tmpBean.operatorName = Convert.ToString(tmpRow["operator"]);
                    tmpBean.opdt = Convert.ToDateTime(tmpRow["opdt"]);
                    tmpBean.note = Convert.ToString(tmpRow["note"]);
                    if (tmpRow["packcount"] != DBNull.Value)
                    {
                        tmpBean.packcount = Convert.ToInt16(tmpRow["packcount"]);
                    }
                    if (tmpRow["codecount"] != DBNull.Value)
                    {
                        tmpBean.codecount = Convert.ToInt16(tmpRow["codecount"]);
                    }
                    retList.Add(tmpBean);
                }
            }
            return retList;
        }

        /// <summary>
        /// 获取待终检密封箱表信息
        /// </summary>
        /// <returns></returns>
        public List<ZjmfPreInfo> getZjmfPreInfo()
        {
            List<ZjmfPreInfo> retList = new List<ZjmfPreInfo>();
            DataSet retSet = db01.GetDataSet(CommandType.Text, "select * from tblzjmf_pre where ismf='未密' and delbz='0'", new MySqlParameter[] { });
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                foreach (DataRow tmpRow in dt.Rows)
                {
                    ZjmfPreInfo tmpBean = new ZjmfPreInfo();
                    tmpBean.listID = Convert.ToInt32(tmpRow["listID"]);
                    tmpBean.meters_code = Convert.ToString(tmpRow["meters_code"]);
                    tmpBean.meters_directions = Convert.ToString(tmpRow["meters_directions"]);
                    tmpBean.meters_IDstart = Convert.ToString(tmpRow["meters_IDstart"]);
                    tmpBean.meters_IDend = Convert.ToString(tmpRow["meters_IDend"]);
                    tmpBean.customer_code = Convert.ToString(tmpRow["customer_code"]);
                    tmpBean.ismf = Convert.ToString(tmpRow["ismf"]);
                    tmpBean.delbz = Convert.ToString(tmpRow["delbz"]);
                    tmpBean.operatorName = Convert.ToString(tmpRow["operator"]);
                    tmpBean.opdt = Convert.ToString(tmpRow["opdt"]);
                    tmpBean.note = Convert.ToString(tmpRow["note"]);
                    retList.Add(tmpBean);
                }
            }
            return retList;
        }

        /// <summary>
        /// 获取客户信息
        /// </summary>
        /// <returns></returns>
        public List<CustomerInfo> getCustomerInfo()
        {
            List<CustomerInfo> retList = new List<CustomerInfo>();
            DataSet retSet = db01.GetDataSet(CommandType.Text, "select * from tblcustomer", new MySqlParameter[] { });
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                foreach (DataRow tmpRow in dt.Rows)
                {
                    CustomerInfo tmpBean = new CustomerInfo();
                    tmpBean.customer_code = Convert.ToString(tmpRow["customer_code"]);
                    tmpBean.customer_name = Convert.ToString(tmpRow["customer_name"]);
                    tmpBean.opdt = Convert.ToString(tmpRow["opdt"]);
                    tmpBean.delbz = Convert.ToString(tmpRow["delbz"]);
                    tmpBean.note = Convert.ToString(tmpRow["note"]);
                    retList.Add(tmpBean);
                }
            }
            return retList;
        }

        /// <summary>
        /// 获取最新零件码信息
        /// </summary>
        /// <returns></returns>
        public BarCodeInfo getBaseLatestInfo()
        {
            DataSet retSet = db01.GetDataSet(CommandType.Text, "select * from base_barcode order by OpDt desc limit 1,1", new MySqlParameter[] { });
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    BarCodeInfo retInfo = new BarCodeInfo();
                    retInfo.part1 = Convert.ToString(dt.Rows[0]["part1"]);
                    retInfo.part2 = Convert.ToString(dt.Rows[0]["part2"]);
                    retInfo.part3 = Convert.ToString(dt.Rows[0]["part3"]);
                    return retInfo;
                }
            }
            return null;
        }

        /// <summary>
        /// 上传基本二维码信息数据
        /// </summary>
        /// <param name="lstBarCode"></param>
        /// <returns>处理结果</returns>
        public int uploadBaseBarCode(List<BarCodeInfo> lstBarCode)
        {
            MySqlConnection conn = null;
            MySqlTransaction uploadBarTrans = null;
            int updCnt = 0;
            int insCnt = 0;
            try
            {
                conn = db01.getConnection();
                conn.Open();
                uploadBarTrans = conn.BeginTransaction();

                string existSql = "select count(1) from base_barcode where barcodeID = @barcodeID";
                string updBarCodeSql = "UPDATE base_barcode SET mp_number = @mp_number, boxid = @boxid, part1=@part1, part2=@part2, part3=@part3 WHERE barcodeID = @barcodeID AND Operator_Name = @Operator_Name AND TO_DAYS(OpDt) = TO_DAYS(@OpDt)";
                string insBarCodeSql = "INSERT INTO base_barcode (barcodeID, Operator_Name, OpDt, mp_number, boxid, part1, part2, part3) VALUES (@barcodeID, @Operator_Name, @OpDt, @mp_number, @boxid, @part1, @part2, @part3)";
                foreach (BarCodeInfo tmpBarCode in lstBarCode)
                {
                    MySqlParameter existParam = new MySqlParameter("@barcodeID", tmpBarCode.barcodeID);
                    object existRes = db01.ExecuteScalar(conn, CommandType.Text, existSql, new MySqlParameter[] { existParam });

                    MySqlParameter[] sqlParams = new MySqlParameter[8];
                    sqlParams[0] = new MySqlParameter("@barcodeID", tmpBarCode.barcodeID);
                    sqlParams[1] = new MySqlParameter("@Operator_Name", tmpBarCode.Operator_Name);
                    sqlParams[2] = new MySqlParameter("@OpDt", tmpBarCode.OpDt);
                    sqlParams[3] = new MySqlParameter("@mp_number", tmpBarCode.mp_number);
                    sqlParams[4] = new MySqlParameter("@boxid", tmpBarCode.boxid);
                    sqlParams[5] = new MySqlParameter("@part1", tmpBarCode.part1);
                    sqlParams[6] = new MySqlParameter("@part2", tmpBarCode.part2);
                    sqlParams[7] = new MySqlParameter("@part3", tmpBarCode.part3);
                    if (Convert.ToInt32(existRes) > 0)
                    {
                        // 存在则更新
                        updCnt += db01.ExecuteNonQuery(uploadBarTrans, CommandType.Text, updBarCodeSql, sqlParams);
                    }
                    else
                    {
                        // 服务器数据库不存在，执行插入操作
                        insCnt += db01.ExecuteNonQuery(uploadBarTrans, CommandType.Text, insBarCodeSql, sqlParams);
                    }
                }
                LogUtil.Info("二维码信息插入数据：" + insCnt);
                LogUtil.Info("二维码信息更新数据：" + updCnt);

                string getConnectSql = "select connect_text from connect";
                object objVal = db01.ExecuteScalar(conn, CommandType.Text, getConnectSql, new MySqlParameter[] { });
                if (Consts.CONNECT_SUCCESS.Equals(Convert.ToString(objVal)))
                {
                    LogUtil.Info("上传事务提交。");
                    // 提交
                    uploadBarTrans.Commit();
                    GlobalVal.bolNetConnect = true;
                    return Consts.RET_SUCCESS;
                }
                else
                {
                    LogUtil.Info("上传事务回滚。");
                    // 回滚
                    uploadBarTrans.Rollback();
                    GlobalVal.bolNetConnect = false;
                }
            }
            catch (Exception ex)
            {
                LogUtil.Error(ex);
                if (uploadBarTrans != null)
                {
                    uploadBarTrans.Rollback();
                }
            }
            finally
            {
                try
                {
                    if (conn != null && conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
                catch (Exception e)
                {
                    LogUtil.Error(e);
                }
            }
            return Consts.RET_FAIL;
        }

        /// <summary>
        /// 网络通断变量
        /// </summary>
        /// <returns></returns>
        public string getConnetText()
        {
            string getConnectSql = "select connect_text from connect";
            object objVal = db01.ExecuteScalar(CommandType.Text, getConnectSql, new MySqlParameter[] { });
            return objVal.ToString();
        }

        /// <summary>
        /// 获取指定用户当日的工作量
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public int getWorkAmount(string userName)
        {
            MySqlParameter param = new MySqlParameter("@Operator_Name", userName);
            int diffHour = GlobalVal.Diff_Hour;
            int nextDayDiffHour = diffHour + 24;
            string amountSql = "select count(Operator_Name) from base_barcode where Operator_Name=@Operator_Name AND OpDt > date(date_add(now(), interval " + diffHour + " hour)) ";
            LogUtil.Debug("获取工作量SQL：" + amountSql);
            object amount = db01.ExecuteScalar(CommandType.Text, amountSql, new MySqlParameter[] { param });
            return Convert.ToInt32(amount);
        }

        /// <summary>
        /// 获取指定用户当日的机芯机壳工作量
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public int getJXJKWorkAmount(string userName)
        {
            MySqlParameter param = new MySqlParameter("@Operator_Name", userName);
            int diffHour = GlobalVal.Diff_Hour;
            int nextDayDiffHour = diffHour + 24;
            string amountSql = "select count(Operator_Name) from jxjk_barcode where Operator_Name=@Operator_Name AND OpDt > date(date_add(now(), interval " + GlobalVal.Diff_Hour + " hour)) and mark_repair='合格' ";
            object amount = db01.ExecuteScalar(CommandType.Text, amountSql, new MySqlParameter[] { param });
            return Convert.ToInt32(amount);
        }

        /// <summary>
        /// 获取指定用户当日的机壳工作量
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public int getJKWorkAmount(string userName)
        {
            MySqlParameter param = new MySqlParameter("@Operator_Name", userName);
            string amountSql = "select count(Operator_Name) from jk_barcode where Operator_Name=@Operator_Name AND date(OpDt) = date(date_add(now(), interval " + GlobalVal.Diff_Hour + " hour))";
            object amount = db01.ExecuteScalar(CommandType.Text, amountSql, new MySqlParameter[] { param });
            return Convert.ToInt32(amount);
        }

        /// <summary>
        /// 获取指定用户当日的封箱工作量
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public int getPackWorkAmount(string userName)
        {
            MySqlParameter param = new MySqlParameter("@Operator_Name", userName);
            string amountSql = "select count(Operator_Name) from pack_barcode where Operator_Name=@Operator_Name AND delbz='0' ";

            string getSysDtSql = "select DATE_FORMAT(date_add(now(), interval " + GlobalVal.Diff_Hour + " hour),'%H%i%s')";
            string strSysTime = Convert.ToString(db01.ExecuteScalar(getSysDtSql));
            LogUtil.Info("获取服务器系统时间：" + strSysTime);
            if (strSysTime.CompareTo(GlobalVal.WORK_START_TIME) < 0)
            {
                //amountSql += " AND DATE_FORMAT(OpDt,'%Y%m%d%H%i%s') >= DATE_FORMAT(date_add(now(), interval " + (GlobalVal.Diff_Hour - 24) + " hour), '%Y%m%d" + GlobalVal.WORK_START_TIME + "')";
                //amountSql += " AND DATE_FORMAT(OpDt,'%Y%m%d%H%i%s') < DATE_FORMAT(date_add(now(), interval " + GlobalVal.Diff_Hour + " hour), '%Y%m%d" + GlobalVal.WORK_START_TIME + "')";

                amountSql += " AND OpDt between str_to_date(DATE_FORMAT(DATE_ADD(now(), interval " + (GlobalVal.Diff_Hour - 24) + " hour), '%Y%m%d" + GlobalVal.WORK_START_TIME + "'),'%Y%m%d%H%i%s') ";
                amountSql += "          and str_to_date(DATE_FORMAT(date_add(now(), interval " + GlobalVal.Diff_Hour + " hour), '%Y%m%d" + GlobalVal.WORK_START_TIME + "'),'%Y%m%d%H%i%s') ";

            }
            else
            {
                //amountSql += " AND DATE_FORMAT(OpDt,'%Y%m%d%H%i%s') >= DATE_FORMAT(date_add(now(), interval " + GlobalVal.Diff_Hour + " hour), '%Y%m%d" + GlobalVal.WORK_START_TIME + "')";
                //amountSql += " AND DATE_FORMAT(OpDt,'%Y%m%d%H%i%s') < DATE_FORMAT(date_add(now(), interval " + (GlobalVal.Diff_Hour + 24) + " hour), '%Y%m%d" + GlobalVal.WORK_START_TIME + "')";
                amountSql += " AND OpDt between str_to_date(DATE_FORMAT(date_add(now(), interval " + GlobalVal.Diff_Hour + " hour), '%Y%m%d" + GlobalVal.WORK_START_TIME + "'),'%Y%m%d%H%i%s') ";
                amountSql += "          and str_to_date(DATE_FORMAT(date_add(now(), interval " + (GlobalVal.Diff_Hour + 24) + " hour), '%Y%m%d" + GlobalVal.WORK_START_TIME + "'),'%Y%m%d%H%i%s') ";

            }
            LogUtil.Info("获取封箱工作量SQL：" + amountSql);
            object amount = db01.ExecuteScalar(CommandType.Text, amountSql, new MySqlParameter[] { param });
            return Convert.ToInt32(amount);
        }

        /// <summary>
        /// 获取偏移量
        /// </summary>
        /// <returns></returns>
        public int getOffSetValue(string workPosition)
        {
            MySqlParameter param = new MySqlParameter("@workposition", workPosition);
            string getSql = "select offsetvalue from tbloffset where workposition=@workposition";
            object OffSetValue = db01.ExecuteScalar(CommandType.Text, getSql, new MySqlParameter[] { param });
            if (OffSetValue == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(OffSetValue);
            }
        }

        /// <summary>
        /// 获取生产线信息
        /// </summary>
        /// <returns></returns>
        public List<LineInfo> getLineInfo()
        {
            List<LineInfo> retList = new List<LineInfo>();
            DataSet retSet = db01.GetDataSet(CommandType.Text, "SELECT lineno, linename, tablename,concat_ws(' ', lineno,linename) disName FROM line_name", new MySqlParameter[] { });
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                foreach (DataRow tmpRow in dt.Rows)
                {
                    LineInfo tmpBean = new LineInfo();
                    tmpBean.lineNo = Convert.ToString(tmpRow["lineno"]);
                    tmpBean.lineName = Convert.ToString(tmpRow["linename"]);
                    tmpBean.tableName = Convert.ToString(tmpRow["tablename"]);
                    tmpBean.disName = Convert.ToString(tmpRow["disName"]);
                    retList.Add(tmpBean);
                }
            }
            return retList;
        }

        /// <summary>
        /// 获取最新零件码信息
        /// </summary>
        /// <returns></returns>
        public JXJKBarCodeInfo getJXJKLatestInfo()
        {
            DataSet retSet = db01.GetDataSet(CommandType.Text, "select * from jxjk_barcode order by OpDt desc limit 1,1", new MySqlParameter[] { });
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    JXJKBarCodeInfo retInfo = new JXJKBarCodeInfo();
                    retInfo.UpShellCode = Convert.ToString(dt.Rows[0]["up_shell"]);
                    retInfo.DownShellCode = Convert.ToString(dt.Rows[0]["down_shell"]);
                    return retInfo;
                }
            }
            return null;
        }

        /// <summary>
        /// 上传机芯壳二维码信息数据
        /// </summary>
        /// <param name="lstBarCode"></param>
        /// <returns>处理结果</returns>
        public int uploadJKJXBarCode(List<JXJKBarCodeInfo> lstBarCode)
        {
            MySqlConnection conn = null;
            MySqlTransaction uploadBarTrans = null;
            int insCnt = 0;
            try
            {
                conn = db01.getConnection();
                conn.Open();
                uploadBarTrans = conn.BeginTransaction();

                string jxExistSql = "select count(1) from jxjk_barcode where jx_barcodeID = @jx_barcodeID and mark_repair='合格' ";
                string jkExistSql = "select count(1) from jxjk_barcode where jk_barcodeID = @jk_barcodeID and mark_repair='合格' ";
                string delJxSql = "delete from jxjk_barcode where jx_barcodeID = @jx_barcodeID and mark_repair='合格'";
                string delJkSql = "delete from jxjk_barcode where jk_barcodeID = @jk_barcodeID and mark_repair='合格'";
                string insJXJKSql = "INSERT INTO jxjk_barcode (jx_barcodeID, jk_barcodeID, vavle_barcodeID, Operator_Name, OpDt, mark_repair, up_shell, down_shell, frame1,frame2) VALUES (@jx_barcodeID, @jk_barcodeID, @vavle_barcodeID, @Operator_Name, @OpDt, @mark_repair, @UpShellCode, @DownShellCode,@frame1,@frame2)";
                foreach (JXJKBarCodeInfo tmpBarCode in lstBarCode)
                {
                    MySqlParameter existJxParam = new MySqlParameter("@jx_barcodeID", tmpBarCode.JxBarcodeId);
                    object existJxRes = db01.ExecuteScalar(conn, CommandType.Text, jxExistSql, new MySqlParameter[] { existJxParam });
                    if (Convert.ToInt32(existJxRes) > 0 && "合格".Equals(tmpBarCode.markRepair))
                    {
                        GlobalVal.jkErrCode = GlobalVal.jkErrCode + " " + tmpBarCode.JkBarcodeId;
                        GlobalVal.jxErrCode = GlobalVal.jxErrCode + " " + tmpBarCode.JxBarcodeId;
                        db01.ExecuteNonQuery(uploadBarTrans, CommandType.Text, delJxSql, existJxParam);
                        GlobalVal.bolRepeats = true;
                    }

                    MySqlParameter existJkParam = new MySqlParameter("@jk_barcodeID", tmpBarCode.JkBarcodeId);
                    object existJkRes = db01.ExecuteScalar(conn, CommandType.Text, jkExistSql, new MySqlParameter[] { existJkParam });
                    if (Convert.ToInt32(existJkRes) > 0 && "合格".Equals(tmpBarCode.markRepair))
                    {
                        GlobalVal.jkErrCode = GlobalVal.jkErrCode + " " + tmpBarCode.JkBarcodeId;
                        GlobalVal.jxErrCode = GlobalVal.jxErrCode + " " + tmpBarCode.JxBarcodeId;
                        db01.ExecuteNonQuery(uploadBarTrans, CommandType.Text, delJkSql, existJkParam);
                        GlobalVal.bolRepeats = true;
                    }

                    MySqlParameter[] sqlParams = new MySqlParameter[10];
                    sqlParams[0] = new MySqlParameter("@jx_barcodeID", tmpBarCode.JxBarcodeId);
                    sqlParams[1] = new MySqlParameter("@jk_barcodeID", tmpBarCode.JkBarcodeId);
                    sqlParams[2] = new MySqlParameter("@vavle_barcodeID", tmpBarCode.VavleBarcodeId);
                    sqlParams[3] = new MySqlParameter("@Operator_Name", tmpBarCode.OperatorName);
                    sqlParams[4] = new MySqlParameter("@OpDt", tmpBarCode.OpDt);
                    sqlParams[5] = new MySqlParameter("@mark_repair", tmpBarCode.markRepair);
                    sqlParams[6] = new MySqlParameter("@UpShellCode", tmpBarCode.UpShellCode);
                    sqlParams[7] = new MySqlParameter("@DownShellCode", tmpBarCode.DownShellCode);
                    sqlParams[8] = new MySqlParameter("@frame1", tmpBarCode.frame1Code);
                    sqlParams[9] = new MySqlParameter("@frame2", tmpBarCode.frame2Code);
                    insCnt += db01.ExecuteNonQuery(uploadBarTrans, CommandType.Text, insJXJKSql, sqlParams);

                    if (GlobalVal.bolUploadSubMain == false)
                    {
                        GlobalVal.jxjkCountI_OK = GlobalVal.jxjkCountI_OK + 1;
                    }
                }
                LogUtil.Info("机芯机壳重复数据：" + GlobalVal.jxErrCode + " " + GlobalVal.jkErrCode);
                LogUtil.Info("机芯机壳二维码信息插入数据：" + insCnt);

                string getConnectSql = "select connect_text from connect";
                object objVal = db01.ExecuteScalar(conn, CommandType.Text, getConnectSql, new MySqlParameter[] { });
                if (Consts.CONNECT_SUCCESS.Equals(Convert.ToString(objVal)))
                {
                    LogUtil.Info("上传事务提交。");
                    // 提交
                    uploadBarTrans.Commit();
                    GlobalVal.bolNetConnect = true;
                    return Consts.RET_SUCCESS;
                }
                else
                {
                    LogUtil.Info("上传事务回滚。");
                    // 回滚
                    uploadBarTrans.Rollback();
                    GlobalVal.bolNetConnect = false;
                }
            }
            catch (Exception ex)
            {
                LogUtil.Error(ex);
                if (uploadBarTrans != null)
                {
                    uploadBarTrans.Rollback();
                }
            }
            finally
            {
                try
                {
                    if (conn != null && conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
                catch (Exception e)
                {
                    LogUtil.Error(e);
                }
            }
            return Consts.RET_FAIL;
        }



        /// <summary>
        /// 上传机壳二维码信息数据
        /// </summary>
        /// <param name="lstBarCode"></param>
        /// <returns>处理结果</returns>
        public int uploadJKBarCode(List<JKBarCodeInfo> lstBarCode)
        {
            MySqlConnection conn = null;
            MySqlTransaction uploadBarTrans = null;
            int updCnt = 0;
            int insCnt = 0;
            try
            {
                conn = db01.getConnection();
                conn.Open();
                uploadBarTrans = conn.BeginTransaction();

                string existSql = "select count(1) from jk_barcode where jk_barcodeID = @barcodeID ";
                string updBarCodeSql = "UPDATE jk_barcode SET Operator_Name = @Operator_Name, OpDt = @OpDt WHERE jk_barcodeID = @barcodeID ";
                string insBarCodeSql = "INSERT INTO jk_barcode (jk_barcodeID, Operator_Name, OpDt) VALUES (@barcodeID, @Operator_Name, @OpDt) ";
                foreach (JKBarCodeInfo tmpBarCode in lstBarCode)
                {
                    MySqlParameter existParam = new MySqlParameter("@barcodeID", tmpBarCode.JkBarcodeId);
                    object existRes = db01.ExecuteScalar(conn, CommandType.Text, existSql, new MySqlParameter[] { existParam });

                    MySqlParameter[] sqlParams = new MySqlParameter[3];
                    sqlParams[0] = new MySqlParameter("@barcodeID", tmpBarCode.JkBarcodeId);
                    sqlParams[1] = new MySqlParameter("@Operator_Name", tmpBarCode.OperatorName);
                    sqlParams[2] = new MySqlParameter("@OpDt", tmpBarCode.OpDt);
                    if (Convert.ToInt32(existRes) > 0)
                    {
                        // 存在则更新
                        updCnt += db01.ExecuteNonQuery(uploadBarTrans, CommandType.Text, updBarCodeSql, sqlParams);

                        if (GlobalVal.bolUploadSubMain == false)
                        {
                            GlobalVal.jkCountI_OK = GlobalVal.jkCountI_OK - 1;
                        }
                    }
                    else
                    {
                        // 服务器数据库不存在，执行插入操作
                        insCnt += db01.ExecuteNonQuery(uploadBarTrans, CommandType.Text, insBarCodeSql, sqlParams);
                    }
                }
                LogUtil.Info("机壳信息插入数据：" + insCnt);
                LogUtil.Info("机壳信息更新数据：" + updCnt);

                string getConnectSql = "select connect_text from connect";
                object objVal = db01.ExecuteScalar(conn, CommandType.Text, getConnectSql, new MySqlParameter[] { });
                if (Consts.CONNECT_SUCCESS.Equals(Convert.ToString(objVal)))
                {
                    LogUtil.Info("上传事务提交。");
                    // 提交
                    uploadBarTrans.Commit();
                    GlobalVal.bolNetConnect = true;
                    return Consts.RET_SUCCESS;
                }
                else
                {
                    LogUtil.Info("上传事务回滚。");
                    // 回滚
                    uploadBarTrans.Rollback();
                    GlobalVal.bolNetConnect = false;
                }
            }
            catch (Exception ex)
            {
                LogUtil.Error(ex);
                if (uploadBarTrans != null)
                {
                    uploadBarTrans.Rollback();
                }
            }
            finally
            {
                try
                {
                    if (conn != null && conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
                catch (Exception e)
                {
                    LogUtil.Error(e);
                }
            }
            return Consts.RET_FAIL;
        }




        /// <summary>
        /// 上传封箱条码信息数据
        /// </summary>
        /// <param name="lstBarCode"></param>
        /// <returns>处理结果</returns>
        public int uploadPackBarCode(DataTable dtPack, int intAmount)
        {
            MySqlConnection conn = null;
            MySqlTransaction uploadBarTrans = null;
            int insCnt = 0;
            try
            {
                conn = db01.getConnection();
                conn.Open();
                uploadBarTrans = conn.BeginTransaction();

                BarCodeMdbDao barcodeMdbDao = new BarCodeMdbDao();
                foreach (DataRow drPack in dtPack.Rows)
                {
                    if (!"0".Equals(drPack["delbz"]))
                    {
                        LogUtil.Debug("delbz不为零所以不上传：pack_barcodeID=" + drPack["pack_barcodeID"] + "  OpDt=" + drPack["OpDt"]);
                        continue;
                    }

                    // 存在完全相同数据直接跳过
                    string existSql = "select count(0) from pack_barcode where pack_barcodeID=@pack_barcodeID and date_format(OpDt, '%Y%m%d%H%i%s')=date_format(@OpDt, '%Y%m%d%H%i%s') and customer_name=@customer_name";
                    MySqlParameter[] existParams = new MySqlParameter[3];
                    existParams[0] = new MySqlParameter("@pack_barcodeID", drPack["pack_barcodeID"]);
                    existParams[1] = new MySqlParameter("@OpDt", drPack["OpDt"]);
                    existParams[2] = new MySqlParameter("@customer_name", drPack["customer_name"]);
                    object existCnt = db01.ExecuteScalar(conn, existSql, existParams);
                    if (existCnt != null && Convert.ToInt32(existCnt) > 0)
                    {
                        LogUtil.Debug("Mysql数据库存在相同记录：pack_barcodeID=" + drPack["pack_barcodeID"] + "  OpDt=" + drPack["OpDt"]);
                        continue;
                    }

                    // 存在数据直接更新
                    string updDelbzSql = "update pack_barcode set delbz='1' where pack_barcodeID=@pack_barcodeID and delbz='0'";
                    MySqlParameter updDelbzparam = new MySqlParameter("@pack_barcodeID", drPack["pack_barcodeID"]);
                    int updDelbzCnt = db01.ExecuteNonQuery(uploadBarTrans, CommandType.Text, updDelbzSql, updDelbzparam);
                    if (updDelbzCnt == 0)
                    {
                        LogUtil.Debug("Mysql数据库不存在记录: pack_barcodeID=" + drPack["pack_barcodeID"]);
                    }

                    string EMPTY_CODE = "";
                    if (Convert.ToString(drPack["customer_name"]) == "合肥燃气集团")
                    {
                        EMPTY_CODE = "400000000000";
                    }
                    else if (Convert.ToString(drPack["customer_name"]) == "沈阳燃气公司")
                    {
                        EMPTY_CODE = "20000000000";
                    }
                    else
                    {
                        EMPTY_CODE = "200000000000";
                    }

                    for (int i = 0; i <= intAmount; i++)
                    {

                        string meter_barcodeID = Convert.ToString(drPack["meter" + (i + 1) + "_barcodeID"]);

                        if (meter_barcodeID != "" && meter_barcodeID != EMPTY_CODE)
                        {

                            //string existSql = "select pack_barcodeID, count(1) repeatCnt from pack_barcode where delbz='0' and (meter1_barcodeID=@meter_barcodeID or meter2_barcodeID=@meter_barcodeID or meter3_barcodeID=@meter_barcodeID or meter4_barcodeID=@meter_barcodeID ";
                            //if (intAmount == 5)
                            //{
                            //    existSql = existSql + " or meter5_barcodeID=@meter_barcodeID  or meter6_barcodeID=@meter_barcodeID )";
                            //}
                            //else
                            //{
                            //    existSql = existSql + " ) ";
                            //}
                            //MySqlParameter[] getParam = new MySqlParameter[1];
                            //getParam[0] = new MySqlParameter("@meter_barcodeID", meter_barcodeID);

                            //DataSet retSet = db01.GetDataSet(CommandType.Text, existSql, getParam);
                            //if (retSet != null && retSet.Tables.Count > 0 && retSet.Tables[0].Rows.Count > 0)
                            //{
                            //    LogUtil.Error("封箱条码重复扫描！ meter1_barcodeID=" + meter_barcodeID);
                            //    barcodeMdbDao.deleteRepeatPack(intAmount, meter_barcodeID);
                            //    if (GlobalVal.bolUploadSubMain)
                            //    {
                            //        continue;
                            //    }
                            //    else
                            //    {
                            //        return Consts.RET_FAIL;
                            //    }
                            //}

                            string getSql = "select count(0) from pack_barcode where meter1_barcodeID=@meter_barcodeID and delbz='0' ";
                            MySqlParameter getParam = new MySqlParameter("@meter_barcodeID", meter_barcodeID);
                            object retObj = db01.ExecuteScalar(conn, getSql, getParam);
                            if (retObj != null && Convert.ToInt32(retObj) > 0)
                            {
                                LogUtil.Error("封箱条码重复扫描！ meter1_barcodeID=" + meter_barcodeID);
                                barcodeMdbDao.deleteRepeatPack(i + 1, meter_barcodeID);
                                if (GlobalVal.bolUploadSubMain)
                                {
                                    continue;
                                }
                                else
                                {
                                    return Consts.RET_FAIL;
                                }
                            }

                            getSql = "select count(0) from pack_barcode where meter2_barcodeID=@meter_barcodeID and delbz='0' ";
                            retObj = db01.ExecuteScalar(conn, getSql, getParam);
                            if (retObj != null && Convert.ToInt32(retObj) > 0)
                            {
                                LogUtil.Error("封箱条码重复扫描！ meter2_barcodeID=" + meter_barcodeID);
                                barcodeMdbDao.deleteRepeatPack(i + 1, meter_barcodeID);
                                if (GlobalVal.bolUploadSubMain)
                                {
                                    continue;
                                }
                                else
                                {
                                    return Consts.RET_FAIL;
                                }
                            }

                            getSql = "select count(0) from pack_barcode where meter3_barcodeID=@meter_barcodeID and delbz='0' ";
                            retObj = db01.ExecuteScalar(conn, getSql, getParam);
                            if (retObj != null && Convert.ToInt32(retObj) > 0)
                            {
                                LogUtil.Error("封箱条码重复扫描！ meter3_barcodeID=" + meter_barcodeID);
                                barcodeMdbDao.deleteRepeatPack(i + 1, meter_barcodeID);
                                if (GlobalVal.bolUploadSubMain)
                                {
                                    continue;
                                }
                                else
                                {
                                    return Consts.RET_FAIL;
                                }
                            }

                            getSql = "select count(0) from pack_barcode where meter4_barcodeID=@meter_barcodeID and delbz='0' ";
                            retObj = db01.ExecuteScalar(conn, getSql, getParam);
                            if (retObj != null && Convert.ToInt32(retObj) > 0)
                            {
                                LogUtil.Error("封箱条码重复扫描！ meter4_barcodeID=" + meter_barcodeID);
                                barcodeMdbDao.deleteRepeatPack(i + 1, meter_barcodeID);
                                if (GlobalVal.bolUploadSubMain)
                                {
                                    continue;
                                }
                                else
                                {
                                    return Consts.RET_FAIL;
                                }
                            }

                            if (intAmount > 3)
                            {
                                getSql = "select count(0) from pack_barcode where meter5_barcodeID=@meter_barcodeID and delbz='0' ";
                                retObj = db01.ExecuteScalar(conn, getSql, getParam);
                                if (retObj != null && Convert.ToInt32(retObj) > 0)
                                {
                                    LogUtil.Error("封箱条码重复扫描！ meter5_barcodeID=" + meter_barcodeID);
                                    barcodeMdbDao.deleteRepeatPack(i + 1, meter_barcodeID);
                                    if (GlobalVal.bolUploadSubMain)
                                    {
                                        continue;
                                    }
                                    else
                                    {
                                        return Consts.RET_FAIL;
                                    }
                                }

                                getSql = "select count(0) from pack_barcode where meter6_barcodeID=@meter_barcodeID and delbz='0' ";
                                retObj = db01.ExecuteScalar(conn, getSql, getParam);
                                if (retObj != null && Convert.ToInt32(retObj) > 0)
                                {
                                    LogUtil.Error("封箱条码重复扫描！ meter6_barcodeID=" + meter_barcodeID);
                                    barcodeMdbDao.deleteRepeatPack(i + 1, meter_barcodeID);
                                    if (GlobalVal.bolUploadSubMain)
                                    {
                                        continue;
                                    }
                                    else
                                    {
                                        return Consts.RET_FAIL;
                                    }
                                }
                            }
                        }
                    }

                    string insPackSql = "INSERT INTO pack_barcode (pack_barcodeID,meter1_barcodeID,meter2_barcodeID,meter3_barcodeID,meter4_barcodeID,meter5_barcodeID,meter6_barcodeID,Operator_Name,delbz,OpDt,customer_name) VALUES (@pack_barcodeID,@meter1_barcodeID,@meter2_barcodeID,@meter3_barcodeID,@meter4_barcodeID,@meter5_barcodeID,@meter6_barcodeID,@Operator_Name,@delbz,@OpDt,@customer_name)";
                    MySqlParameter[] sqlParams = new MySqlParameter[11];
                    sqlParams[0] = new MySqlParameter("@pack_barcodeID", drPack["pack_barcodeID"]);
                    sqlParams[1] = new MySqlParameter("@meter1_barcodeID", drPack["meter1_barcodeID"]);
                    sqlParams[2] = new MySqlParameter("@meter2_barcodeID", drPack["meter2_barcodeID"]);
                    sqlParams[3] = new MySqlParameter("@meter3_barcodeID", drPack["meter3_barcodeID"]);
                    sqlParams[4] = new MySqlParameter("@meter4_barcodeID", drPack["meter4_barcodeID"]);
                    sqlParams[5] = new MySqlParameter("@meter5_barcodeID", drPack["meter5_barcodeID"]);
                    sqlParams[6] = new MySqlParameter("@meter6_barcodeID", drPack["meter6_barcodeID"]);
                    sqlParams[7] = new MySqlParameter("@Operator_Name", drPack["Operator_Name"]);
                    sqlParams[8] = new MySqlParameter("@delbz", drPack["delbz"]);
                    sqlParams[9] = new MySqlParameter("@OpDt", drPack["OpDt"]);
                    sqlParams[10] = new MySqlParameter("@customer_name", drPack["customer_name"]);
                    insCnt += db01.ExecuteNonQuery(uploadBarTrans, CommandType.Text, insPackSql, sqlParams);

                }
                barcodeMdbDao.Release();
                LogUtil.Info("封箱条码信息插入数据：" + insCnt);

                string getConnectSql = "select connect_text from connect";
                object objVal = db01.ExecuteScalar(conn, CommandType.Text, getConnectSql, new MySqlParameter[] { });
                if (Consts.CONNECT_SUCCESS.Equals(Convert.ToString(objVal)))
                {
                    LogUtil.Info("上传事务提交。");
                    // 提交
                    uploadBarTrans.Commit();
                    GlobalVal.bolNetConnect = true;
                    return Consts.RET_SUCCESS;
                }
                else
                {
                    LogUtil.Info("上传事务回滚。");
                    // 回滚
                    uploadBarTrans.Rollback();
                    GlobalVal.bolNetConnect = false;
                }
            }
            catch (Exception ex)
            {
                LogUtil.Error(ex);
                if (uploadBarTrans != null)
                {
                    uploadBarTrans.Rollback();
                }
            }
            finally
            {
                try
                {
                    if (conn != null && conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
                catch (Exception e)
                {
                    LogUtil.Error(e);
                }
            }
            return Consts.RET_FAIL;
        }


        /// <summary>
        /// 获得当前封箱条码起始编号
        /// </summary>
        /// <returns></returns>
        public string getStartPackBarcodeId(string range_PackStart, string range_PackEnd)
        {
            string packBarcodeId = range_PackStart;
            string selectSql = "select CONVERT(IFNULL(MAX(pack_barcodeID) + 1,@range_PackStart) using utf8) pack_barcodeID from pack_barcode where pack_barcodeID between @range_PackStart and @range_PackEnd and delbz='0' ";
            MySqlParameter[] sqlParams = new MySqlParameter[2];
            sqlParams[0] = new MySqlParameter("@range_PackStart", range_PackStart);
            sqlParams[1] = new MySqlParameter("@range_PackEnd", range_PackEnd);
            //DataSet retSet = db01.GetDataSet(CommandType.Text, selectSql, sqlParams);
            //if (retSet != null && retSet.Tables.Count > 0)
            //{
            //    DataTable dt = retSet.Tables[0];
            //    packBarcodeId = Convert.ToString(Convert.ToInt64(dt.Rows[0][0]));
            //}
            LogUtil.Info("获取封箱条码起始编号Sql：" + selectSql);
            object retObj = db01.ExecuteScalar(selectSql, sqlParams);

            if (retObj != null)
            {
                LogUtil.Debug("从Mysql获得当前封箱条码起始编号：" + Convert.ToString(retObj));
                packBarcodeId = Convert.ToString(retObj);
            }

            return packBarcodeId;
        }

        /// <summary>
        /// 判断表号是否存在
        /// </summary>
        public bool existBH(string strBH, int intAmount)
        {
            string existSql = "select 1 from pack_barcode where delbz='0' and (meter1_barcodeID=@meter_barcodeID or meter2_barcodeID=@meter_barcodeID or meter3_barcodeID=@meter_barcodeID or meter4_barcodeID=@meter_barcodeID ";
            if (intAmount == 5)
            {
                existSql = existSql + " or meter5_barcodeID=@meter_barcodeID  or meter6_barcodeID=@meter_barcodeID) ";
            }
            else
            {
                existSql = existSql + " ) ";
            }
            MySqlParameter[] sqlParams = new MySqlParameter[1];
            sqlParams[0] = new MySqlParameter("@meter_barcodeID", strBH);

            DataSet retSet = db01.GetDataSet(CommandType.Text, existSql, sqlParams);
            if (retSet != null && retSet.Tables.Count > 0 && retSet.Tables[0].Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获取本次封箱的所有表号
        /// </summary>
        /// <param name="range_MeterStart"></param>
        /// <param name="range_MeterEnd"></param>
        /// <param name="range_PackStart"></param>
        /// <param name="range_PackEnd"></param>
        /// <returns></returns>
        public List<String> getAllBH(string range_MeterStart, string range_MeterEnd, string range_PackStart, string range_PackEnd, ref List<String> lstAllXH)
        {
            string selectSql = "select * from pack_barcode where delbz='0' and pack_barcodeID >=@range_PackStart and pack_barcodeID <= @range_PackEnd ";
            //selectSql += " and meter1_barcodeID >= @range_MeterStart and meter1_barcodeID <= @range_MeterEnd "; // 2020.10.10 Del
            MySqlParameter[] sqlParams = new MySqlParameter[2];
            sqlParams[0] = new MySqlParameter("@range_PackStart", range_PackStart);
            sqlParams[1] = new MySqlParameter("@range_PackEnd", range_PackEnd);
            //sqlParams[2] = new MySqlParameter("@range_MeterStart", range_MeterStart);
            //sqlParams[3] = new MySqlParameter("@range_MeterEnd", range_MeterEnd);
            DataSet retSet = db01.GetDataSet(CommandType.Text, selectSql, sqlParams);

            List<String> retList = new List<String>();
            if (retSet != null && retSet.Tables.Count > 0 && retSet.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < retSet.Tables[0].Rows.Count; i++)
                {
                    DataRow tmpDR = retSet.Tables[0].Rows[i];
                    retList.Add(tmpDR["meter1_barcodeID"].ToString());
                    retList.Add(tmpDR["meter2_barcodeID"].ToString());
                    retList.Add(tmpDR["meter3_barcodeID"].ToString());
                    retList.Add(tmpDR["meter4_barcodeID"].ToString());
                    if (tmpDR["meter5_barcodeID"] != null && !"".Equals(tmpDR["meter5_barcodeID"].ToString()))
                    {
                        retList.Add(tmpDR["meter5_barcodeID"].ToString());
                    }
                    if (tmpDR["meter6_barcodeID"] != null && !"".Equals(tmpDR["meter6_barcodeID"].ToString()))
                    {
                        retList.Add(tmpDR["meter6_barcodeID"].ToString());
                    }

                    // 箱号
                    lstAllXH.Add(tmpDR["pack_barcodeID"].ToString());
                }
            }
            return retList;
        }

        /// <summary>
        ///  封箱完成
        /// </summary>
        /// <param name="drPack.Cells"></param>
        public int completePack(DataGridViewRow drPack)
        {
            try
            {
                // 2020.10.10 删除istest条件
                //string updSql = "update tblmnoprn_pre set delbz='1' where istest='已测' and ispack='未封' and meters_IDstart=@meters_IDstart and meters_IDend=@meters_IDend and delbz='0'";
                string updSql = "update tblmnoprn_pre set delbz='1' where ispack='未封' and meters_IDstart=@meters_IDstart and meters_IDend=@meters_IDend and delbz='0'";
                MySqlParameter[] updParams = new MySqlParameter[2];
                updParams[0] = new MySqlParameter("@meters_IDstart", drPack.Cells["meters_IDstart"].Value);
                updParams[1] = new MySqlParameter("@meters_IDend", drPack.Cells["meters_IDend"].Value);
                int updCnt = db01.ExecuteNonQuery(CommandType.Text, updSql, updParams);
                LogUtil.Debug("已测未封更新(" + updCnt + ")：meters_IDstart=" + drPack.Cells["meters_IDstart"].Value + "  meters_IDend=" + drPack.Cells["meters_IDend"].Value);
                if (updCnt == 0)
                {
                    return Consts.RET_FAIL;
                }

                string insSql = "INSERT INTO tblmnoprn_pre (orderID,meters_code,meters_directions,meters_IDstart,meters_IDend,packID_start,packID_end,meters_tolmax,meters_tolmin,customer_code,meters_ploss,istest,ispack,delbz,operator,opdt,note,packcount,codecount) "
                    + " VALUES "
                    + "(@orderID,@meters_code,@meters_directions,@meters_IDstart,@meters_IDend,@packID_start,@packID_end,@meters_tolmax,@meters_tolmin,@customer_code,@meters_ploss,@istest,@ispack,@delbz,@operator,sysdate(),@note,@packcount,@codecount) ";
                MySqlParameter[] insParams = new MySqlParameter[18];
                insParams[0] = new MySqlParameter("@orderID", drPack.Cells["orderID"].Value);
                insParams[1] = new MySqlParameter("@meters_code", drPack.Cells["meters_code"].Value);
                insParams[2] = new MySqlParameter("@meters_directions", drPack.Cells["meters_directions"].Value);
                insParams[3] = new MySqlParameter("@meters_IDstart", drPack.Cells["meters_IDstart"].Value);
                insParams[4] = new MySqlParameter("@meters_IDend", drPack.Cells["meters_IDend"].Value);
                insParams[5] = new MySqlParameter("@packID_start", drPack.Cells["packID_start"].Value);
                insParams[6] = new MySqlParameter("@packID_end", drPack.Cells["packID_end"].Value);
                insParams[7] = new MySqlParameter("@meters_tolmax", drPack.Cells["meters_tolmax"].Value);
                insParams[8] = new MySqlParameter("@meters_tolmin", drPack.Cells["meters_tolmin"].Value);
                insParams[9] = new MySqlParameter("@customer_code", drPack.Cells["customer_code"].Value);
                insParams[10] = new MySqlParameter("@meters_ploss", drPack.Cells["meters_ploss"].Value);
                insParams[11] = new MySqlParameter("@istest", "已测");
                insParams[12] = new MySqlParameter("@ispack", "已封");
                insParams[13] = new MySqlParameter("@delbz", "0");
                insParams[14] = new MySqlParameter("@operator", GlobalVal.CurrentUser.User);
                //insParams[15] = new MySqlParameter("@opdt", DateTime.Now);
                insParams[15] = new MySqlParameter("@note", drPack.Cells["note"].Value);
                insParams[16] = new MySqlParameter("@packcount", drPack.Cells["packcount"].Value);
                insParams[17] = new MySqlParameter("@codecount", drPack.Cells["codecount"].Value);
                int insCnt = db01.ExecuteNonQuery(CommandType.Text, insSql, insParams);
                LogUtil.Debug("已测已封插入(" + insCnt + ")：meters_IDstart=" + drPack.Cells["meters_IDstart"].Value + "  meters_IDend=" + drPack.Cells["meters_IDend"].Value);
                if (insCnt == 0)
                {
                    return Consts.RET_FAIL;
                }
            }
            catch (Exception e)
            {
                LogUtil.Error(e);
                return Consts.RET_FAIL;
            }
            return Consts.RET_SUCCESS;
        }



        /// <summary>
        /// 获取重复表号
        /// </summary>
        public List<PackRepeatItem> getRepeatBH(string strBH, int intAmount)
        {
            string existSql = "select pack_barcodeID, count(1) repeatCnt from pack_barcode where delbz='0' and (meter1_barcodeID=@meter_barcodeID or meter2_barcodeID=@meter_barcodeID or meter3_barcodeID=@meter_barcodeID or meter4_barcodeID=@meter_barcodeID ";
            if (intAmount == 5)
            {
                existSql = existSql + " or meter5_barcodeID=@meter_barcodeID  or meter6_barcodeID=@meter_barcodeID )";
            }
            else
            {
                existSql = existSql + " ) ";
            }
            existSql = existSql + " group by pack_barcodeID order by pack_barcodeID ";
            MySqlParameter[] sqlParams = new MySqlParameter[1];
            sqlParams[0] = new MySqlParameter("@meter_barcodeID", strBH);

            List<PackRepeatItem> retList = new List<PackRepeatItem>();
            DataSet retSet = db01.GetDataSet(CommandType.Text, existSql, sqlParams);
            if (retSet != null && retSet.Tables.Count > 0 && retSet.Tables[0].Rows.Count > 0)
            {
                if (retSet.Tables[0].Rows.Count > 1)
                {
                    for (int i = 1; i < retSet.Tables[0].Rows.Count; i++)
                    {
                        DataRow tmpDr = retSet.Tables[0].Rows[i];
                        PackRepeatItem item = new PackRepeatItem();
                        item.repeatBH = strBH;
                        item.locatXH = Convert.ToString(tmpDr["pack_barcodeID"]);
                        retList.Add(item);
                    }
                }
                else
                {
                    DataRow tmpDr = retSet.Tables[0].Rows[0];
                    if (Convert.ToInt32(tmpDr["repeatCnt"]) > 1)
                    {
                        PackRepeatItem item = new PackRepeatItem();
                        item.repeatBH = strBH;
                        item.locatXH = Convert.ToString(tmpDr["pack_barcodeID"]);
                        retList.Add(item);
                    }
                }
            }
            return retList;
        }

        /// <summary>
        /// 获取本次封箱的所有表号以及所在箱号
        /// </summary>
        /// <param name="range_MeterStart"></param>
        /// <param name="range_MeterEnd"></param>
        /// <param name="range_PackStart"></param>
        /// <param name="range_PackEnd"></param>
        /// <returns></returns>
        public List<PackRepeatItem> getAllBHAndLocation(string range_MeterStart, string range_MeterEnd, string range_PackStart, string range_PackEnd)
        {
            string selectSql = "select * from pack_barcode where delbz='0' and pack_barcodeID >=@range_PackStart and pack_barcodeID <= @range_PackEnd ";
            selectSql += " and meter1_barcodeID >= @range_MeterStart and meter1_barcodeID <= @range_MeterEnd ";
            MySqlParameter[] sqlParams = new MySqlParameter[4];
            sqlParams[0] = new MySqlParameter("@range_PackStart", range_PackStart);
            sqlParams[1] = new MySqlParameter("@range_PackEnd", range_PackEnd);
            sqlParams[2] = new MySqlParameter("@range_MeterStart", range_MeterStart);
            sqlParams[3] = new MySqlParameter("@range_MeterEnd", range_MeterEnd);
            DataSet retSet = db01.GetDataSet(CommandType.Text, selectSql, sqlParams);

            List<PackRepeatItem> retList = new List<PackRepeatItem>();
            if (retSet != null && retSet.Tables.Count > 0 && retSet.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < retSet.Tables[0].Rows.Count; i++)
                {
                    DataRow tmpDR = retSet.Tables[0].Rows[i];
                    PackRepeatItem tmpItem1 = new PackRepeatItem();
                    tmpItem1.locatXH = Convert.ToString(tmpDR["pack_barcodeID"]);
                    tmpItem1.repeatBH = Convert.ToString(tmpDR["meter1_barcodeID"]);
                    retList.Add(tmpItem1);

                    PackRepeatItem tmpItem2 = new PackRepeatItem();
                    tmpItem2.locatXH = Convert.ToString(tmpDR["pack_barcodeID"]);
                    tmpItem2.repeatBH = Convert.ToString(tmpDR["meter2_barcodeID"]);
                    retList.Add(tmpItem2);

                    PackRepeatItem tmpItem3 = new PackRepeatItem();
                    tmpItem3.locatXH = Convert.ToString(tmpDR["pack_barcodeID"]);
                    tmpItem3.repeatBH = Convert.ToString(tmpDR["meter3_barcodeID"]);
                    retList.Add(tmpItem3);

                    PackRepeatItem tmpItem4 = new PackRepeatItem();
                    tmpItem4.locatXH = Convert.ToString(tmpDR["pack_barcodeID"]);
                    tmpItem4.repeatBH = Convert.ToString(tmpDR["meter4_barcodeID"]);
                    retList.Add(tmpItem4);

                    if (tmpDR["meter5_barcodeID"] != null && !"".Equals(tmpDR["meter5_barcodeID"].ToString()))
                    {
                        PackRepeatItem tmpItem5 = new PackRepeatItem();
                        tmpItem5.locatXH = Convert.ToString(tmpDR["pack_barcodeID"]);
                        tmpItem5.repeatBH = Convert.ToString(tmpDR["meter5_barcodeID"]);
                        retList.Add(tmpItem5);
                    }
                    if (tmpDR["meter6_barcodeID"] != null && !"".Equals(tmpDR["meter6_barcodeID"].ToString()))
                    {
                        PackRepeatItem tmpItem6 = new PackRepeatItem();
                        tmpItem6.locatXH = Convert.ToString(tmpDR["pack_barcodeID"]);
                        tmpItem6.repeatBH = Convert.ToString(tmpDR["meter6_barcodeID"]);
                        retList.Add(tmpItem6);
                    }
                }
            }
            return retList;
        }

        /// <summary>
        /// 换码
        /// </summary>
        /// <param name="strCode"></param>
        /// <param name="barcodeId"></param>
        /// <param name="intAmount"></param>
        /// <returns></returns>
        public int ChangePackCode(string[] strCode, string barcodeId, int intAmount)
        {
            try
            {
                string updSql = "UPDATE pack_barcode SET pack_barcodeID = @pack_barcodeID, meter1_barcodeID = @meter1_barcodeID, meter2_barcodeID = @meter2_barcodeID, meter3_barcodeID = @meter3_barcodeID, meter4_barcodeID = @meter4_barcodeID,  ";

                if (intAmount == 5)
                {
                    updSql = updSql + " meter5_barcodeID = @meter5_barcodeID, meter6_barcodeID = @meter6_barcodeID, ";
                }
                updSql = updSql + " OpDt = sysdate() WHERE pack_barcodeID = @pack_barcodeID AND delbz='0'  ";
                MySqlParameter[] sqlParams = new MySqlParameter[7];
                sqlParams[0] = new MySqlParameter("@pack_barcodeID", barcodeId);
                sqlParams[1] = new MySqlParameter("@meter1_barcodeID", strCode[0]);
                sqlParams[2] = new MySqlParameter("@meter2_barcodeID", strCode[1]);
                sqlParams[3] = new MySqlParameter("@meter3_barcodeID", strCode[2]);
                sqlParams[4] = new MySqlParameter("@meter4_barcodeID", strCode[3]);
                sqlParams[5] = new MySqlParameter("@meter5_barcodeID", strCode[4]);
                sqlParams[6] = new MySqlParameter("@meter6_barcodeID", strCode[5]);
                int updCnt = db01.ExecuteNonQuery(CommandType.Text, updSql, sqlParams);
                return updCnt;
            }
            catch (Exception e)
            {
                LogUtil.Error(e);
                return 0;
            }
        }

        /// <summary>
        /// 根据表号获取补打信息
        /// </summary>
        public bool getPrintInfoByBH(string strBH, int intAmount, PrintBarCodeInfo printInfo)
        {
            string existSql = "select * from pack_barcode where delbz='0' and (meter1_barcodeID=@meter_barcodeID or meter2_barcodeID=@meter_barcodeID or meter3_barcodeID=@meter_barcodeID or meter4_barcodeID=@meter_barcodeID ";
            if (intAmount == 5)
            {
                existSql = existSql + " or meter5_barcodeID=@meter_barcodeID  or meter6_barcodeID=@meter_barcodeID) ";
            }
            else
            {
                existSql = existSql + " ) ";
            }
            MySqlParameter[] sqlParams = new MySqlParameter[1];
            sqlParams[0] = new MySqlParameter("@meter_barcodeID", strBH);

            DataSet retSet = db01.GetDataSet(CommandType.Text, existSql, sqlParams);
            if (retSet != null && retSet.Tables.Count > 0 && retSet.Tables[0].Rows.Count > 0)
            {
                DataRow dr = retSet.Tables[0].Rows[0];
                string[] strCode = new string[6];
                strCode[0] = Convert.ToString(dr["meter1_barcodeID"]);
                strCode[1] = Convert.ToString(dr["meter2_barcodeID"]);
                strCode[2] = Convert.ToString(dr["meter3_barcodeID"]);
                strCode[3] = Convert.ToString(dr["meter4_barcodeID"]);
                strCode[4] = Convert.ToString(dr["meter5_barcodeID"]);
                strCode[5] = Convert.ToString(dr["meter6_barcodeID"]);
                printInfo.strCode = strCode;
                printInfo.packCode = Convert.ToString(dr["pack_barcodeID"]);
                printInfo.strCustomer = Convert.ToString(dr["customer_name"]);
                return true;
            }
            return false;

        }

        /// <summary>
        /// 根据箱号获取补打信息
        /// </summary>
        public bool getPrintInfoByXH(string strXH, PrintBarCodeInfo printInfo)
        {
            string existSql = "select * from pack_barcode where delbz='0' and pack_barcodeID=@pack_barcodeID ";
            MySqlParameter[] sqlParams = new MySqlParameter[1];
            sqlParams[0] = new MySqlParameter("@pack_barcodeID", strXH);
            DataSet retSet = db01.GetDataSet(CommandType.Text, existSql, sqlParams);

            if (retSet != null && retSet.Tables.Count > 0 && retSet.Tables[0].Rows.Count > 0)
            {
                DataRow dr = retSet.Tables[0].Rows[0];
                string[] strCode = new string[6];
                strCode[0] = Convert.ToString(dr["meter1_barcodeID"]);
                strCode[1] = Convert.ToString(dr["meter2_barcodeID"]);
                strCode[2] = Convert.ToString(dr["meter3_barcodeID"]);
                strCode[3] = Convert.ToString(dr["meter4_barcodeID"]);
                strCode[4] = Convert.ToString(dr["meter5_barcodeID"]);
                strCode[5] = Convert.ToString(dr["meter6_barcodeID"]);
                printInfo.strCode = strCode;
                printInfo.packCode = Convert.ToString(dr["pack_barcodeID"]);
                printInfo.strCustomer = Convert.ToString(dr["customer_name"]);
                return true;
            }
            return false;
        }


        /// <summary>
        /// 检查机芯码是否存在
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public Boolean CheckExistJXCode(String code)
        {
            string existSql = "select 1 from base_barcode where barcodeID=@barcodeID ";
            MySqlParameter[] sqlParams = new MySqlParameter[1];
            sqlParams[0] = new MySqlParameter("@barcodeID", code);
            DataSet retSet = db01.GetDataSet(CommandType.Text, existSql, sqlParams);

            if (retSet != null && retSet.Tables.Count > 0 && retSet.Tables[0].Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 检查机壳码是否存在
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public string getJXCodeByJk(String jkCode)
        {
            string getJxSql = "select jx_barcodeID from jxjk_barcode where jk_barcodeID=@jk_barcodeID ";
            MySqlParameter[] sqlParams = new MySqlParameter[1];
            sqlParams[0] = new MySqlParameter("@jk_barcodeID", jkCode);
            object JxCode = db01.ExecuteScalar(CommandType.Text, getJxSql, sqlParams);
            if (JxCode == null)
            {
                return "";
            }
            else
            {
                return Convert.ToString(JxCode);
            }
        }

        /// <summary>
        /// 检查表号是否已经写入NFC标签
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public string CheckBHWritedByNFC(String code)
        {
            string existSql = "select 1 from tbl_nfc_write where write_code=@write_code ";
            MySqlParameter[] sqlParams = new MySqlParameter[1];
            sqlParams[0] = new MySqlParameter("@write_code", code);
            DataSet retSet = db01.GetDataSet(CommandType.Text, existSql, sqlParams);

            if (retSet != null && retSet.Tables.Count > 0 && retSet.Tables[0].Rows.Count > 0)
            {
                return "";
            }
            else
            {
                return "表号未写入NFC标签";
            }
        }


        /// <summary>
        /// 保存机壳码
        /// </summary>
        /// <param name="lstBarCode"></param>
        /// <returns>处理结果</returns>
        public string saveJKBarCode(JKBarCodeInfo jkBarCodeInfo)
        {

            int insCnt = 0;
            try
            {

                string existSql = "select count(1) from jk_barcode where jk_barcodeID = @barcodeID ";
                string insBarCodeSql = "INSERT INTO jk_barcode (jk_barcodeID, Operator_Name, OpDt) VALUES (@barcodeID, @Operator_Name, @OpDt) ";

                MySqlParameter existParam = new MySqlParameter("@barcodeID", jkBarCodeInfo.JkBarcodeId);
                object existRes = db01.ExecuteScalar(CommandType.Text, existSql, new MySqlParameter[] { existParam });

                MySqlParameter[] sqlParams = new MySqlParameter[3];
                sqlParams[0] = new MySqlParameter("@barcodeID", jkBarCodeInfo.JkBarcodeId);
                sqlParams[1] = new MySqlParameter("@Operator_Name", jkBarCodeInfo.OperatorName);
                sqlParams[2] = new MySqlParameter("@OpDt", jkBarCodeInfo.OpDt);
                if (Convert.ToInt32(existRes) > 0)
                {
                    return "机壳码已经存在";
                }
                else
                {
                    // 服务器数据库不存在，执行插入操作
                    insCnt += db01.ExecuteNonQuery(CommandType.Text, insBarCodeSql, sqlParams);
                }
                LogUtil.Info("机壳信息插入数据：" + insCnt);
               
            }
            catch (Exception ex)
            {
                LogUtil.Error("机壳码插入异常", ex);
            }
            
            return "";
        }


        /// <summary>
        /// 获取表号范围
        /// </summary>
        /// <returns></returns>
        public MnoprnPreInfo getMeterRange(string orderID, string meters_directions)
        {
            MySqlParameter[] sqlParams = new MySqlParameter[2];
            sqlParams[0] = new MySqlParameter("@orderID", orderID);
            sqlParams[1] = new MySqlParameter("@meters_directions", meters_directions);
            DataSet retSet = db01.GetDataSet(CommandType.Text, "select * from tblmnoprn_pre where orderID=@orderID and meters_directions=@meters_directions order by opdt desc limit 0,1", sqlParams);
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    DataRow tmpRow = dt.Rows[0];
                    MnoprnPreInfo tmpBean = new MnoprnPreInfo();
                    tmpBean.orderID = Convert.ToString(tmpRow["orderID"]);
                    tmpBean.meters_directions = Convert.ToString(tmpRow["meters_directions"]);
                    tmpBean.meters_IDstart = Convert.ToString(tmpRow["meters_IDstart"]);
                    tmpBean.meters_IDend = Convert.ToString(tmpRow["meters_IDend"]);
         
                    return tmpBean;
                }
            }
            return null;
        }

        /// <summary>
        /// 获取表型号
        /// </summary>
        /// <returns></returns>
        public MnoprnPreInfo getNotes(string orderID)
        {
            MySqlParameter[] sqlParams = new MySqlParameter[1];
            sqlParams[0] = new MySqlParameter("@orderID", orderID);
            DataSet retSet = db01.GetDataSet(CommandType.Text, "select * from tblmnoprn_pre where orderID=@orderID order by opdt desc limit 0,1", sqlParams);
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    DataRow tmpRow = dt.Rows[0];
                    MnoprnPreInfo tmpBean = new MnoprnPreInfo();
                    tmpBean.orderID = Convert.ToString(tmpRow["orderID"]);
                    tmpBean.program = Convert.ToString(tmpRow["Program"]);
                    return tmpBean;
                }
            }
            return null;
        }
    }
}

